﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Business;
using Model;
using System.Web.Security;
using MB.Util;
using System.Data.OleDb;
using System.Data;
using System.Transactions;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace MvcAppWeb.Controllers
{
    public class LocationController : BaseDataController
    {
        //
        // GET: /Location/
        #region 货位信息
        public ActionResult LocationMain()//主表
        {
            if (Session["UserInfo"] == null) return RedirectToAction("LogOn", "Account");
            else
            {
                AppUserList model = (AppUserList)Session["UserInfo"];
                return View(model);
            }
        }

        public JsonResult LocationCodeList(int IsSetAll)
        {
            var retData = new object();
            BLocation nbl = new BLocation();
            retData = nbl.GetLocationCodeList(IsSetAll).ToList();
            return Json(retData);
        }
        public JsonResult GoodsCodeList(int IsSetAll, int IsSetNone = 1, string Beginwith = "")
        {
            var retData = new object();
            BLocation nbl = new BLocation();
            retData = nbl.GetGoodsCodeList(IsSetAll, IsSetNone, Beginwith).ToList();
            return Json(retData);
        }
        public JsonResult WarehouseList(int IsSetAll)
        {
            var retData = new object();
            BLocation nbl = new BLocation();
            retData = nbl.GetWarehouseList(IsSetAll).ToList();
            return Json(retData);
        }

        /// <summary>
        /// 校验货位编码唯一性
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public string checkLocation()
        {
            string sjson = string.Empty;
            string code = Request["txtLocationCode"];

            BLocation nbl = new BLocation();
            Location checklocation = nbl.CheckLocation(code);
            if (checklocation != null)
            {
                sjson = "{success:false,msg:'该货位编码已存在！'}";
            }
            else
            {
                sjson = "{success:true}";
            }
            return sjson;
        }

        public JsonResult Query(string SWAREHOUSEID, string SLOCATIONCODE, string SLOCATIONTYPE, string SArea,
            string SRoadWay, string SSTATUS)
        {
            int page = int.Parse(Request["page"].ToString());
            int rows = int.Parse(Request["rows"].ToString());
            int total = 0;
            int n = 0;
            string where = " where 1=1";
            if (!string.IsNullOrEmpty(SWAREHOUSEID))
            {
                if (int.TryParse(SWAREHOUSEID, out n))
                {
                    if (Convert.ToInt32(SWAREHOUSEID) != 0)
                    {
                        where += " and l.warehouseid='" + SWAREHOUSEID + "'";
                    }
                }
                else
                {
                    where += " and 1=2";
                }
            }
            if (!string.IsNullOrEmpty(SLOCATIONCODE))
            {
                where += " and l.LOCATIONCODE like'%" + SLOCATIONCODE + "%'";
            }
            if (!string.IsNullOrEmpty(SLOCATIONTYPE))
            {
                where += " and l.LOCATIONTYPE like'%" + SLOCATIONTYPE + "%'";
            }
            if (!string.IsNullOrEmpty(SArea))
            {
                where += " and l.Area like'%" + SArea + "%'";
            }
            if (!string.IsNullOrEmpty(SRoadWay))
            {
                where += " and l.RoadWay like'%" + SRoadWay + "%'";
            }
            if (!string.IsNullOrEmpty(SSTATUS))
            {
                where += " and l.STATUS ='" + SSTATUS + "'";
            }
            BLocation nc = new BLocation();
            var result = nc.GetLocationList(page, rows, out total, where);

            Dictionary<string, object> json = new Dictionary<string, object>();
            json.Add("total", total);
            json.Add("rows", result);
            return Json(json, JsonRequestBehavior.AllowGet);
        }

        [HttpPost]
        public string Saves(string LOCATIONID)
        {
            string sjson = string.Empty;
            try
            {
                AppUserList model = (AppUserList)Session["UserInfo"];
                string LocationCode = Request["txtLocationCode"];
                string LocationType = Request["LocationType"];
                string GOODSRACK = Request["txtGOODSRACK"];
                string Tier = Request["txtTier"];
                string Columns = Request["txtColumns"];
                string Area = Request["txtArea"];
                string RoadWay = Request["txtRoadWay"];
                string Volume = Request["txtVolume"];
                string Remark = Request["txtRemark"];
                string WarehouseID = Request["WAREHOUSEID"];
                string CellNo = Request["CellNo"];
                Location location = new Location();

                sjson = CheckLocation(LOCATIONID, LocationCode, LocationType, WarehouseID, CellNo, GOODSRACK, Tier, Columns, Area,
                    RoadWay, Volume, Remark, out location);

                if (!string.IsNullOrEmpty(sjson))
                    return sjson;

                BLocation nbl = new BLocation();
                if (string.IsNullOrEmpty(LOCATIONID))
                {
                    nbl.AddLocation(location);
                }
                else
                {
                    location.LOCATIONID = int.Parse(LOCATIONID);
                    nbl.EditLocationByID(location);
                }
                sjson = "{success:true}";
            }
            catch (Exception ex)
            {
                sjson = "{success:false,msg:'" + ex.Message + "'}";
            }
            return sjson;
        }

        [HttpPost]
        public string Delete(string ids)
        {
            string sjson = string.Empty;
            try
            {
                BLocation nbl = new BLocation();
                foreach (var id in ids.Split(','))
                {
                    nbl.DeleteLocation(int.Parse(id));
                }
                sjson = "{success:true}";
            }
            catch (Exception ex)
            {
                sjson = "{success:false,msg:'" + ex.Message + "'}";
            }
            return sjson;

        }

        [HttpPost]
        public string ImportLocation(HttpPostedFileBase filebase)
        {
            string sjson = string.Empty;
            HttpPostedFileBase file = Request.Files["files"];
            DataTable table = new DataTable();
            string importStr = string.Empty;
            sjson = ImportFile(file, out table, out importStr);
            if (!string.IsNullOrEmpty(sjson))
                return sjson;

            TransactionOptions transactionOption = new TransactionOptions();

            //设置事务隔离级别
            transactionOption.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;

            // 设置事务超时时间为600秒
            transactionOption.Timeout = new TimeSpan(0, 0, 600);

            //引用事务机制，出错时，事物回滚
            using (TransactionScope transaction = new TransactionScope(TransactionScopeOption.Required, transactionOption))
            {
                try
                {
                    if (!string.IsNullOrEmpty(importStr))//上传的是TXT
                    {
                        List<string> lstStr = importStr.Split(';').ToList();
                        foreach (string item in lstStr)
                        {
                            if (string.IsNullOrEmpty(item))
                                continue;
                            List<string> lstModelStr = item.Split(',').ToList();
                            if (lstModelStr.Count != 11)
                            {
                                sjson = "{success:false,msg:'TXT文件内容格式不正确！'}";
                                return sjson;
                            }
                            string LocationCode = lstModelStr[0].ToString();
                            string LocationType = lstModelStr[1].ToString();
                            string WarehouseID = lstModelStr[2].ToString();
                            string CellNo = lstModelStr[3].ToString();
                            string GOODSRACK = lstModelStr[4].ToString();
                            string Tier = lstModelStr[5].ToString();
                            string Columns = lstModelStr[6].ToString();
                            string Area = lstModelStr[7].ToString();
                            string RoadWay = lstModelStr[8].ToString();
                            string Volume = lstModelStr[9].ToString();
                            string Remark = lstModelStr[10].ToString();
                            Location location = new Location();

                            sjson = CheckLocation(string.Empty, LocationCode, LocationType, WarehouseID, CellNo, GOODSRACK, Tier, Columns, Area,
                                RoadWay, Volume, Remark, out location);

                            if (!string.IsNullOrEmpty(sjson))
                                return sjson;

                            BLocation nbl = new BLocation();
                            nbl.AddLocation(location);
                        }
                    }
                    else
                    {
                        for (int i = 0; i < table.Rows.Count; i++)
                        {
                            string LocationCode = table.Rows[i][0].ToString();
                            string LocationType = table.Rows[i][1].ToString();
                            string WarehouseID = table.Rows[i][3].ToString();
                            string CellNo = table.Rows[i][4].ToString();
                            string GOODSRACK = table.Rows[i][5].ToString();
                            string Tier = table.Rows[i][6].ToString();
                            string Columns = table.Rows[i][7].ToString();
                            string Area = table.Rows[i][8].ToString();
                            string RoadWay = table.Rows[i][9].ToString();
                            string Volume = table.Rows[i][10].ToString();
                            string Remark = table.Rows[i][16].ToString();
                            Location location = new Location();

                            sjson = CheckLocation(string.Empty, LocationCode, LocationType, WarehouseID, CellNo, GOODSRACK, Tier, Columns, Area,
                                RoadWay, Volume, Remark, out location);

                            if (!string.IsNullOrEmpty(sjson))
                                return sjson;

                            BLocation nbl = new BLocation();
                            nbl.AddLocation(location);
                        }
                    }
                    transaction.Complete();
                }
                catch (Exception ex)
                {
                    sjson = "{success:false,msg:'" + ex.Message + "'}";
                    return sjson;
                }
            }
            sjson = "{success:true}";
            System.Threading.Thread.Sleep(2000);
            return sjson;
        }

        public FileResult ExportExcel(int page, int rows)
        {
            //创建Excel文件的对象
            HSSFWorkbook book = new HSSFWorkbook();
            //添加一个sheet
            ISheet sheet1 = book.CreateSheet("Sheet1");
            #region 获取list数据
            int n = 0;
            int total = 0;
            string SWAREHOUSEID = Request["SWAREHOUSEID"];
            string SLOCATIONCODE = Request["txtSLOCATIONCODE"];
            string SLOCATIONTYPE = Request["SLocationType"];
            string SArea = Request["txtSArea"];
            string SRoadWay = Request["txtSRoadWay"];
            string where = " where 1=1";

            if (!string.IsNullOrEmpty(SWAREHOUSEID))
            {
                if (int.TryParse(SWAREHOUSEID, out n))
                {
                    if (Convert.ToInt32(SWAREHOUSEID) != 0)
                    {
                        where += " and l.warehouseid='" + SWAREHOUSEID + "'";
                    }
                }
                else
                {
                    where += " and 1=2";
                }
            }
            if (!string.IsNullOrEmpty(SLOCATIONCODE))
            {
                where += " and l.LOCATIONCODE like'%" + SLOCATIONCODE + "%'";
            }
            if (!string.IsNullOrEmpty(SLOCATIONTYPE))
            {
                where += " and l.LOCATIONTYPE like'%" + SLOCATIONTYPE + "%'";
            }
            if (!string.IsNullOrEmpty(SArea))
            {
                where += " and l.Area like'%" + SArea + "%'";
            }
            if (!string.IsNullOrEmpty(SRoadWay))
            {
                where += " and l.RoadWay like'%" + SRoadWay + "%'";
            }
            BLocation nc = new BLocation();
            List<Location> list = nc.GetLocationList(page, int.MaxValue, out total, where);
            #endregion
            //给sheet1添加第一行的头部标题
            IRow row1 = sheet1.CreateRow(0);
            List<string> lstTitle = new List<string> { "货位编码", "货位类型", "仓库", "仓库ID", "库位", "货架", "层", "列", "区域", "巷道", 
                "体积", "创建人", "创建时间", "修改人", "修改时间", "状态", "备注" };
            for (int i = 0; i < lstTitle.Count; i++)
            {
                row1.CreateCell(i).SetCellValue(lstTitle[i]);
            }
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < list.Count; i++)
            {
                IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(list[i].LOCATIONCODE);
                rowtemp.CreateCell(1).SetCellValue(list[i].LOCATIONTYPE);
                rowtemp.CreateCell(2).SetCellValue(list[i].WAREHOUSENAME);
                rowtemp.CreateCell(3).SetCellValue(list[i].WAREHOUSEID);
                rowtemp.CreateCell(4).SetCellValue(list[i].CELLNO);
                rowtemp.CreateCell(5).SetCellValue(list[i].GOODSRACK);
                rowtemp.CreateCell(6).SetCellValue(list[i].TIER.ToString());
                rowtemp.CreateCell(7).SetCellValue(list[i].COLUMNS.ToString());
                rowtemp.CreateCell(8).SetCellValue(list[i].AREA);
                rowtemp.CreateCell(9).SetCellValue(list[i].ROADWAY);
                rowtemp.CreateCell(10).SetCellValue(list[i].VOLUME.ToString());
                rowtemp.CreateCell(11).SetCellValue(list[i].CREATER);
                rowtemp.CreateCell(12).SetCellValue(list[i].CREATEDATE.ToString());
                rowtemp.CreateCell(13).SetCellValue(list[i].EDITOR);
                rowtemp.CreateCell(14).SetCellValue(list[i].EDITDATE.ToString());
                rowtemp.CreateCell(15).SetCellValue(list[i].STATUS == "0" ? "失效" : "生效");
                rowtemp.CreateCell(16).SetCellValue(list[i].REMARK);
            }
            // 写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return File(ms, "application/vnd.ms-excel", NewTimeStamp("Location"));
        }

        private string CheckLocation(string LOCATIONID, string LocationCode, string LocationType, string WarehouseID, string CellNo, string GOODSRACK, string Tier, string Columns,
            string Area, string RoadWay, string Volume, string Remark, out Location location)
        {
            string sjson = string.Empty;
            AppUserList model = (AppUserList)Session["UserInfo"];

            location = new Location();
            if (string.IsNullOrEmpty(LocationCode))
            {
                sjson = "{success:false,msg:'货位编码不能为空:" + LocationCode + "！'}";
                return sjson;
            }
            if (string.IsNullOrEmpty(LocationType))
            {
                sjson = "{success:false,msg:'货位类型不能为空:" + LocationCode + "！'}";
                return sjson;
            }
            if (string.IsNullOrEmpty(CellNo) && LocationType != "逻辑货位")
            {
                sjson = "{success:false,msg:'库位不能为空:" + LocationCode + "！'}";
                return sjson;
            }
            if (string.IsNullOrEmpty(WarehouseID))
            {
                sjson = "{success:false,msg:'仓库不能为空:" + LocationCode + "！'}";
                return sjson;
            }
            location.LOCATIONTYPE = LocationType;
            int TWarehouseID = 0;
            if (int.TryParse(WarehouseID, out TWarehouseID))
                location.WAREHOUSEID = TWarehouseID;
            location.GOODSRACK = GOODSRACK;
            decimal TTier = 0;
            if (decimal.TryParse(Tier, out TTier))
                location.TIER = TTier;
            decimal TColumns = 0;
            if (decimal.TryParse(Columns, out TColumns))
                location.COLUMNS = TColumns;
            location.AREA = Area;
            location.ROADWAY = RoadWay;
            decimal TVolume = 0;
            if (decimal.TryParse(Volume, out TVolume))
                location.VOLUME = TVolume;
            location.REMARK = Remark;
            location.LOCATIONCODE = LocationCode;
            location.CREATER = model.USERNAME;
            location.CREATEDATE = DateTime.Now;
            location.EDITOR = model.USERNAME;
            location.EDITDATE = DateTime.Now;
            location.CELLNO = CellNo;
            BLocation nbl = new BLocation();
            Location checklocation = nbl.CheckLocation(LocationCode, LOCATIONID);

            if (checklocation != null)
            {
                sjson = "{success:false,msg:'该货位编码已存在:" + LocationCode + "！'}";
                return sjson;
            }
            return sjson;
        }
        #endregion

        #region 库位商品关联
        public ActionResult LocationGoods()//库位商品关联
        {
            if (Session["UserInfo"] == null) return RedirectToAction("LogOn", "Account");
            else
            {
                AppUserList model = (AppUserList)Session["UserInfo"];
                return View(model);
            }
        }

        public JsonResult QueryLocationGoods(string SLOCATIONCODE, string SGOODSCODE, string INVGOODSCODE, string SGOODSNAME, string INVGOODSNAME)
        {
            int page = int.Parse(Request["page"].ToString());
            int rows = int.Parse(Request["rows"].ToString());
            int total = 0;
            string where = " where 1=1 and lo.STATUS=1 ";
            if (!string.IsNullOrEmpty(SLOCATIONCODE))
            {
                where += " and lo.LOCATIONCODE like'%" + SLOCATIONCODE + "%'";
            }
            if (!string.IsNullOrEmpty(SGOODSCODE))
            {
                where += " and g.GOODSCODE like'%" + SGOODSCODE + "%'";
            }
            if (!string.IsNullOrEmpty(INVGOODSCODE))
            {
                where += " and g2.GOODSCODE like'%" + INVGOODSCODE + "%'";
            }
            if (!string.IsNullOrEmpty(SGOODSNAME))
            {
                where += " and g.GOODSNAME like'%" + SGOODSNAME + "%'";
            }
            if (!string.IsNullOrEmpty(INVGOODSNAME))
            {
                where += " and g2.GOODSNAME like'%" + INVGOODSNAME + "%'";
            }
            BLocation nc = new BLocation();
            var result = nc.GetLocationGoodsList(page, rows, out total, where);

            Dictionary<string, object> json = new Dictionary<string, object>();
            json.Add("total", total);
            json.Add("rows", result);
            return Json(json, JsonRequestBehavior.AllowGet);
        }

        [HttpPost]
        public string checkLocationExist(string code)
        {
            string sjson = string.Empty;

            BLocation nbl = new BLocation();
            Location checklocation = nbl.CheckLocation(code);
            if (checklocation != null)
            {
                sjson = "{success:true}";
            }
            else
            {
                sjson = "{success:false,msg:'该货位编码不存在！'}";
            }
            return sjson;
        }

        [HttpPost]
        public string checkGoodsExist()
        {
            string sjson = string.Empty;
            string code = Request["txtGoodsCode"];

            BLocation nbl = new BLocation();
            Goods check = nbl.CheckGoods(code);
            if (check != null)
            {
                sjson = "{success:true}";
            }
            else
            {
                sjson = "{success:false,msg:'该商品编码不存在！'}";
            }
            return sjson;
        }

        [HttpPost]
        public string SaveLocationGoods(string LOCATIONGOODSID)
        {
            string sjson = string.Empty;
            try
            {
                string LocationID = Request["LocationCode"];
                string GoodsID = Request["GoodsCode"];
                string ProcessGoodsID = Request["ProcessGoodsCode"];
                string DirectSellingGoodsID = Request["DirectSellingGoodsCode"];
                string WOCGoodsID = Request["WOCGoodsCode"];
                string OtherGoodsID = Request["OtherGoodsCode"];
                string Remark = Request["txtRemark"];
                LocationGoods _LocationGoods = new LocationGoods();
                sjson = CheckLocationGoods(LOCATIONGOODSID, string.Empty, LocationID, string.Empty, GoodsID,
                    string.Empty, ProcessGoodsID, string.Empty, DirectSellingGoodsID, string.Empty, WOCGoodsID, string.Empty, OtherGoodsID,
                    Remark, out _LocationGoods);

                if (!string.IsNullOrEmpty(sjson))
                    return sjson;

                BLocation nbl = new BLocation();
                if (string.IsNullOrEmpty(LOCATIONGOODSID))
                {
                    nbl.AddLocationGoods(_LocationGoods);
                }
                else
                {
                    _LocationGoods.LOCATIONGOODSID = int.Parse(LOCATIONGOODSID);
                    nbl.EditLocationGoodsByID(_LocationGoods);
                }
                sjson = "{success:true}";
            }
            catch (Exception ex)
            {
                sjson = "{success:false,msg:'" + ex.Message + "'}";
            }
            return sjson;
        }

        [HttpPost]
        public string DeleteLocationGoods(string ids)
        {
            string sjson = string.Empty;
            try
            {
                BLocation nbl = new BLocation();
                foreach (var id in ids.Split(','))
                {
                    nbl.DeleteLocationGoods(int.Parse(id));
                }
                sjson = "{success:true}";
            }
            catch (Exception ex)
            {
                sjson = "{success:false,msg:'" + ex.Message + "'}";
            }
            return sjson;

        }

        [HttpPost]
        public string ImportLocationGoods(HttpPostedFileBase filebase)
        {
            string sjson = string.Empty;
            HttpPostedFileBase file = Request.Files["files"];
            DataTable table = new DataTable();
            string importStr = string.Empty;
            sjson = ImportFile(file, out table, out importStr);
            if (!string.IsNullOrEmpty(sjson))
                return sjson;

            TransactionOptions transactionOption = new TransactionOptions();

            //设置事务隔离级别
            transactionOption.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;

            // 设置事务超时时间为600秒
            transactionOption.Timeout = new TimeSpan(0, 0, 600);

            //引用事务机制，出错时，事物回滚
            using (TransactionScope transaction = new TransactionScope(TransactionScopeOption.Required, transactionOption))
            {
                try
                {
                    if (!string.IsNullOrEmpty(importStr))//上传的是TXT
                    {
                        List<string> lstStr = importStr.Split(';').ToList();
                        foreach (string item in lstStr)
                        {
                            if (string.IsNullOrEmpty(item))
                                continue;
                            List<string> lstModelStr = item.Split(',').ToList();
                            if (lstModelStr.Count != 3)
                            {
                                sjson = "{success:false,msg:'TXT文件内容格式不正确！'}";
                                return sjson;
                            }
                            string LocationCode = lstModelStr[0].ToString();
                            string GoodsCode = lstModelStr[1].ToString();
                            string ProcessGoodsCode = lstModelStr[2].ToString();
                            string DirectSellingGoodsCode = lstModelStr[3].ToString();
                            string WOCGoodsCode = lstModelStr[4].ToString();
                            string OtherGoodsCode = lstModelStr[5].ToString();
                            string Remark = lstModelStr[6].ToString();
                            LocationGoods _LocationGoods = new LocationGoods();
                            sjson = CheckLocationGoods(string.Empty, LocationCode, string.Empty, GoodsCode, string.Empty, ProcessGoodsCode, string.Empty,
                                DirectSellingGoodsCode, string.Empty, WOCGoodsCode, string.Empty, OtherGoodsCode, string.Empty, Remark, out _LocationGoods);
                            if (!string.IsNullOrEmpty(sjson))
                                return sjson;
                            BLocation nbl = new BLocation();
                            nbl.AddLocationGoods(_LocationGoods);
                        }
                    }
                    else
                    {
                        for (int i = 0; i < table.Rows.Count; i++)
                        {
                            string LocationCode = table.Rows[i][0].ToString();
                            string GoodsCode = table.Rows[i][1].ToString();
                            string ProcessGoodsCode = table.Rows[i][7].ToString();
                            string DirectSellingGoodsCode = table.Rows[i][5].ToString();
                            string WOCGoodsCode = table.Rows[i][9].ToString();
                            string OtherGoodsCode = table.Rows[i][11].ToString();
                            string Remark = table.Rows[i][13].ToString();
                            LocationGoods _LocationGoods = new LocationGoods();

                            sjson = CheckLocationGoods(string.Empty, LocationCode, string.Empty, GoodsCode, string.Empty, ProcessGoodsCode, string.Empty,
                                DirectSellingGoodsCode, string.Empty, WOCGoodsCode, string.Empty, OtherGoodsCode, string.Empty, Remark, out _LocationGoods);

                            if (!string.IsNullOrEmpty(sjson))
                                return sjson;
                            BLocation nbl = new BLocation();
                            nbl.AddLocationGoods(_LocationGoods);
                        }
                    }
                    transaction.Complete();
                }
                catch (Exception ex)
                {
                    sjson = "{success:false,msg:'" + ex.Message + "'}";
                    return sjson;
                }
            }
            sjson = "{success:true}";
            System.Threading.Thread.Sleep(2000);
            return sjson;
        }

        [HttpPost]
        public FileResult ExportExcelLocationGoods(int page, int rows)//导出execl
        {
            //创建Excel文件的对象
            HSSFWorkbook book = new HSSFWorkbook();
            //添加一个sheet
            ISheet sheet1 = book.CreateSheet("Sheet1");
            #region 获取list数据
            int total = 0;
            string SLOCATIONCODE = Request["txtSLOCATIONCODE"];
            string SGOODSCODE = Request["txtSGOODSCODE"];
            string INVGOODSCODE = Request["txtINVGOODSCODE"];
            string SGOODSNAME = Request["txtSGOODSNAME"];
            string INVGOODSNAME = Request["txtINVGOODSNAME"];
            string where = " where 1=1";
            if (!string.IsNullOrEmpty(SLOCATIONCODE))
            {
                where += " and lo.LOCATIONCODE like'%" + SLOCATIONCODE + "%'";
            }
            if (!string.IsNullOrEmpty(SGOODSCODE))
            {
                where += " and g.GOODSCODE like'%" + SGOODSCODE + "%'";
            }
            if (!string.IsNullOrEmpty(INVGOODSCODE))
            {
                where += " and g2.GOODSCODE like'%" + INVGOODSCODE + "%'";
            }
            if (!string.IsNullOrEmpty(SGOODSNAME))
            {
                where += " and g.GOODSNAME like'%" + SGOODSNAME + "%'";
            }
            if (!string.IsNullOrEmpty(INVGOODSNAME))
            {
                where += " and g2.GOODSNAME like'%" + INVGOODSNAME + "%'";
            }
            BLocation nc = new BLocation();
            List<LocationGoods> list = nc.GetLocationGoodsList(page, int.MaxValue, out total, where);
            #endregion
            //给sheet1添加第一行的头部标题
            IRow row1 = sheet1.CreateRow(0);
            List<string> lstTitle = new List<string> { "货位编码", "上架商品编码", "上架商品名称", "仓库", "库位",
                "下架商品编码", "下架商品名称", "加工商品编码", "加工商品名称",
                "WOC商品编码", "WOC商品名称","供应商商品编码", "供应商商品名称","备注" };
            for (int i = 0; i < lstTitle.Count; i++)
            {
                row1.CreateCell(i).SetCellValue(lstTitle[i]);
            }
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < list.Count; i++)
            {
                IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(list[i].LOCATIONCODE);
                rowtemp.CreateCell(1).SetCellValue(list[i].GOODSCODE);
                rowtemp.CreateCell(2).SetCellValue(list[i].GOODSNAME);
                rowtemp.CreateCell(3).SetCellValue(list[i].WAREHOUSENAME);
                rowtemp.CreateCell(4).SetCellValue(list[i].CELLNO);
                rowtemp.CreateCell(5).SetCellValue(list[i].DIRECTSELLINGGOODSCODE);
                rowtemp.CreateCell(6).SetCellValue(list[i].DIRECTSELLINGGOODSNAME);
                rowtemp.CreateCell(7).SetCellValue(list[i].PROCESSGOODSCODE);
                rowtemp.CreateCell(8).SetCellValue(list[i].PROCESSGOODSNAME);
                rowtemp.CreateCell(9).SetCellValue(list[i].WOCGOODSCODE);
                rowtemp.CreateCell(10).SetCellValue(list[i].WOCGOODSNAME);
                rowtemp.CreateCell(11).SetCellValue(list[i].OTHERGOODSCODE);
                rowtemp.CreateCell(12).SetCellValue(list[i].OTHERGOODSNAME);
                rowtemp.CreateCell(13).SetCellValue(list[i].REMARK);
            }
            // 写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return File(ms, "application/vnd.ms-excel", NewTimeStamp("LocationGoods"));
        }

        private string CheckLocationGoods(string LOCATIONGOODSID, string LocationCode, string LocationID, string GoodsCode, string GoodsID,
            string ProcessGoodsCode, string ProcessGoodsID, string DirectSellingGoodsCode, string DirectSellingGoodsID,
            string WOCGoodsCode, string WOCGoodsID, string OtherGoodsCode, string OtherGoodsID,
            string Remark, out LocationGoods locationgoods)
        {
            string sjson = string.Empty;
            AppUserList model = (AppUserList)Session["UserInfo"];
            locationgoods = new LocationGoods();
            locationgoods.REMARK = Remark;
            locationgoods.CREATER = model.USERNAME;
            locationgoods.CREATEDATE = DateTime.Now;
            locationgoods.EDITOR = model.USERNAME;
            locationgoods.EDITDATE = DateTime.Now;
            BLocation nbl = new BLocation();
            if (!string.IsNullOrEmpty(LocationCode))
            {
                Location checklocation = nbl.CheckLocation(LocationCode);
                if (checklocation != null)
                {
                    locationgoods.LOCATIONID = checklocation.LOCATIONID;
                }
                else
                {
                    sjson = "{success:false,msg:'货位编码不存在:" + LocationCode + "！'}";
                    return sjson;
                }
            }
            if (!string.IsNullOrEmpty(GoodsCode))
            {
                Goods checkgoods = nbl.CheckGoods(GoodsCode);
                if (checkgoods != null)
                {
                    locationgoods.GOODSID = checkgoods.GOODSID;
                }
                else
                {
                    sjson = "{success:false,msg:'商品编码不存在:" + GoodsCode + "！'}";
                    return sjson;
                }
            }
            if (!string.IsNullOrEmpty(ProcessGoodsCode) && ProcessGoodsCode != "0")
            {
                Goods checkgoods = nbl.CheckGoods(ProcessGoodsCode);
                if (checkgoods != null)
                {
                    locationgoods.PROCESSGOODSID = checkgoods.GOODSID;
                }
                else
                {
                    sjson = "{success:false,msg:'加工商品编码不存在:" + ProcessGoodsCode + "！'}";
                    return sjson;
                }
            }
            if (!string.IsNullOrEmpty(DirectSellingGoodsCode) && DirectSellingGoodsCode != "0")
            {
                Goods checkgoods = nbl.CheckGoods(DirectSellingGoodsCode);
                if (checkgoods != null)
                {
                    locationgoods.DIRECTSELLINGGOODSID = checkgoods.GOODSID;
                }
                else
                {
                    sjson = "{success:false,msg:'上架商品编码不存在:" + DirectSellingGoodsCode + "！'}";
                    return sjson;
                }
            }
            if (!string.IsNullOrEmpty(WOCGoodsCode) && WOCGoodsCode != "0")
            {
                Goods checkgoods = nbl.CheckGoods(WOCGoodsCode);
                if (checkgoods != null)
                {
                    locationgoods.WOCGOODSID = checkgoods.GOODSID;
                }
                else
                {
                    sjson = "{success:false,msg:'WOC商品编码不存在:" + WOCGoodsCode + "！'}";
                    return sjson;
                }
            }
            if (!string.IsNullOrEmpty(OtherGoodsCode) && OtherGoodsCode != "0")
            {
                Goods checkgoods = nbl.CheckGoods(OtherGoodsCode);
                if (checkgoods != null)
                {
                    locationgoods.OTHERGOODSID = checkgoods.GOODSID;
                }
                else
                {
                    sjson = "{success:false,msg:'供应商商品编码不存在:" + OtherGoodsCode + "！'}";
                    return sjson;
                }
            }
            if (!string.IsNullOrEmpty(LocationID))
                locationgoods.LOCATIONID = int.Parse(LocationID);
            if (!string.IsNullOrEmpty(GoodsID))
                locationgoods.GOODSID = int.Parse(GoodsID);
            if (!string.IsNullOrEmpty(ProcessGoodsID))
                locationgoods.PROCESSGOODSID = int.Parse(ProcessGoodsID);
            if (!string.IsNullOrEmpty(DirectSellingGoodsID))
                locationgoods.DIRECTSELLINGGOODSID = int.Parse(DirectSellingGoodsID);
            if (!string.IsNullOrEmpty(WOCGoodsID))
                locationgoods.WOCGOODSID = int.Parse(WOCGoodsID);
            if (!string.IsNullOrEmpty(OtherGoodsID))
                locationgoods.OTHERGOODSID = int.Parse(OtherGoodsID);
            List<LocationGoods> _LocationGoods = nbl.CheckLocationGoodsPrimary(locationgoods.LOCATIONID, LOCATIONGOODSID);
            if (_LocationGoods != null && _LocationGoods.Count > 0)
            {
                if (_LocationGoods.Where(_ => _.WAREHOUSECODE == "06").ToList().Count > 0 ||//货位仓库为分拣仓时，只能对应一个商品
                    _LocationGoods.Where(_ => _.GOODSID == int.Parse(GoodsID)).ToList().Count > 0)//其他仓库根据商品和货位判断唯一性
                    sjson = "{success:false,msg:'货位和商品关联已存在:" + GoodsCode + "！'}";
                return sjson;
            }
            return sjson;
        }
        #endregion
    }
}
